<?php
/**********************************************************************************
WIKINDX: Bibliographic Management system.
Copyright (C)

This program is free software; you can redistribute it and/or modify it under the terms 
of the GNU General Public License as published by the Free Software Foundation; either 
version 2 of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; 
without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. 
See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program; 
if not, write to the 
Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA

The WIKINDX Team 2005
sirfragalot@users.sourceforge.net
**********************************************************************************/

/*
* AdoDB as db abstraction layer for Wikindx.
* Currently, only MySQL and PostgreSQL >= v7.2 are supported.
* For compatibility, the SQL server must have autoincrementing IDs hence just MySQL and PostgreSQL.
*
* NB - postgreSQL folds field names to lowercase unless they're wrapped in double quotes!
*
* CVS keywords
* @author $author$
* @version $version$
*/
class SQL
{
	
	//object last record set (query result)
	var $recordset;
	
	/*
	* Constructor
	*
	* @author Daniel Pozzi
	* @editor Mark Grimshaw
	* @version 2
	* @param none
	* @return boolean
	*/
	function SQL(){
	
		//startup
		include("config.php");
		include_once("core/messages/ERRORS.php");
		$this->errors = new ERRORS();
		
		//set properties
		$this->host = $WIKINDX_DB_HOST;
		$this->user = $WIKINDX_DB_USER;
		$this->pword = $WIKINDX_DB_PASSWORD;
		$this->dbase = $WIKINDX_DB;
		$this->dbaseType = $WIKINDX_DB_TYPE;
		$this->dbPersistent = $WIKINDX_DB_PERSISTENT;
		
		//include adodb and prepare
		include_once("core/sql/adodb.inc.php");
		$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
 		if(!$this->open())
			die($this->errors->text("dbError", "open"));
	}
	function createMySQL()
	{
		include_once("core/sql/adodb-xmlschema.inc.php");
		$schemaFile = 'update/createSQL.xml';
		$schema = new adoSchema($this->db);
		$schema->ContinueOnError( TRUE );
		$schema->ExecuteInline( TRUE );
		$sql = $schema->ParseSchema($schemaFile);
		$result = $schema->ExecuteSchema();
	}
	/*
	* @author Daniel Pozzi
	* @version 1
	* @param none
	* @return boolean
	*/
	function open(){
 		$this->db =& NewADOConnection($this->dbaseType); //$this->dbaseType);
		//"localhost", "root", "password", "mydb"
		if($this->dbPersistent)
			return $this->db->PConnect($this->host, $this->user, $this->pword, $this->dbase);
		else
			return $this->db->Connect($this->host, $this->user, $this->pword, $this->dbase);
	}
	
	/*
	* execute queries
	*
	* @author Daniel Pozzi
	* @modified Mark Grimshaw
	* @version 2
	* @param string query
	* @return object
	*/
	function query($querystring){
//print "$querystring<P>";
		$this->error = $this->errors->text("dbError", "read");
		if(!$recordset = $this->db->Execute($querystring))
			die($this->error);
//print_r($this->recordset);
		global $WIKINDX_DB_QUERIES;
		$WIKINDX_DB_QUERIES++;
		return $recordset;
	}
	
	/*
	* execute queries - ignore error warnings (used with SYSTEMCHECK.php)
	*
	* @author Mark Grimshaw
	* @version 1
	* @param string query
	* @return object
	*/
	function queryNoError($querystring){
//print "$querystring<P>";
		$this->db->Execute($querystring);
//print_r($this->recordset);
		global $WIKINDX_DB_QUERIES;
		$WIKINDX_DB_QUERIES++;
	}
	
	/*
	* return numRows from given or last recordset
	* - just for compatibility, recordset has method RecordCount
	*
	* @author Daniel Pozzi
	* @version 1
	* @param object recordset
	* @return int
	*/
	function numRows(&$recordset){
		return $recordset->recordCount();
	}

	/*
	* @author Mark Grimshaw
	* Actually the same as fetchRow() below but here for compatibility purposes.
	* fetchRow() in adodb.inc.php returns false if $recordset->EOF and automatically increments the 
	* database row counter.
	* @version 2
	* @param array
	* @return array
	*/
	function loopRecordSet(&$recordset)
	{
		return $recordset->fetchRow();
	}
	/*
	* @author Mark Grimshaw
	* @modified Daniel Pozzi
	* @version 2
	* @param array
	* @return array
	*/
	function fetchRow(&$recordset){
		return $recordset->fetchRow();
	}
	
	/*
	* @author Mark Grimshaw
	* @modified Daniel Pozzi
	* @version 2
	* @param array
	* @return string html
	*/
	function fetchOne(&$recordset){
		if(!$row = $recordset->fetchRow())
			return FALSE;
		$key = key($row);
		return $row[$key];
	}
	
	/*
	* @author Mark Grimshaw
	* @modified Daniel Pozzi
	* @version 1
	* @see SQL::select()
	* @param array tables
	* @param array field
	* @param recordset
	*/
	function select($tables, $fields, $conditions = ''){
	
		if(is_array($fields))
			$field = $this->formatFields($fields);
		else
			$field = $fields;
		$table = $this->formatTables($tables);
		$this->error = $this->errors->text("dbError", "read");
		return $this->query("SELECT $field FROM $table $conditions");
	}
	
	/*
	* @author Mark Grimshaw
	* @modified Daniel Pozzi
	* @version 1
	* @see SQL::select()
	* @param array tables
	* @param array field
	* @param recordset
	*/
	function selectWithExceptions($tables, $fields, $conditions = ''){
	
		if(is_array($fields))
			$field = $this->formatFields($fields, TRUE);
		else
			$field = $fields;
		$table = $this->formatTables($tables);
		$this->error = $this->errors->text("dbError", "read");
		return $this->query("SELECT $field FROM $table $conditions");
	}
	
	/*
	* @author Mark Grimshaw
	* @modified Daniel Pozzi
	* @version 1
	* @see SQL::select()
	* @param array tables
	* @param array field
	* @param SQL SELECT string
	*/
	function selectNoExecute($tables, $fields, $conditions = ''){
	
		if(is_array($fields))
			$field = $this->formatFields($fields);
		else
			$field = $fields;
		$table = $this->formatTables($tables);
		$this->error = $this->errors->text("dbError", "read");
		return "SELECT $field FROM $table $conditions";
	}
	
	/*
	* @author Mark Grimshaw
	* @modified Daniel Pozzi
	* @version 1
	* @see SQL::select()
	* @param array tables
	* @param array field
	* @param recordset
	*/
	function limit($sql, $limit, $offset){
		$this->error = $this->errors->text("dbError", "read");
//print "$sql<P>";
		if(!$recordset = $this->db->SelectLimit($sql, $limit, $offset))
			die($this->error);
		return $recordset;
	}
	
	/*
	* @author Mark Grimshaw
	* @modified Daniel Pozzi
	* @version 1
	* @see SQL::insert()
	* @param string table
	* @param array fields
	* @param array values
	* @param string conditions
	*/
	function insert($table, $fields, $values, $conditions = '')
	{
		if(is_array($fields))
			$field = $this->formatFields($fields);
		else
			$field = "`$fields`";
		if($this->dbaseType == 'mysql')
			$table = "`" . $table . "`";
		$value = is_array($values) ? $this->formatValues($values) : $this->tidyInput($values);
		$this->error = $this->errors->text("dbError", "write");
		$this->query("INSERT INTO $table ($field) VALUES($value) $conditions");
	}
	
	/*
	* @author Mark Grimshaw
	* @modified Daniel Pozzi
	* @version 1
	* @see SQL::update()
	* @param string table
	* @param array updateArray
	* @param string conditions
	*/
	function update($table, $updateArray, $conditions = ''){
		$set = $this->formatUpdate($updateArray);
		if($this->dbaseType == 'mysql')
			$table = "`" . $table . "`";
		$this->error = $this->errors->text("dbError", "write");
		$this->query("UPDATE $table $set $conditions");
	}
	
	/*
	* @author Mark Grimshaw
	* @modified Daniel Pozzi
	* @version 1
	* @param string table
	* @param string set statement
	* @param string conditions
	*/
	function updateSingle($table, $set, $conditions = ''){
		$this->error = $this->errors->text("dbError", "write");
		if($this->dbaseType == 'mysql')
			$table = "`" . $table . "`";
		$this->query("UPDATE $table SET $set $conditions");
	}
	
	/*
	* @author Mark Grimshaw
	* @modified Daniel Pozzi
	* @version 1
	* @see SQL::updateNull()
	* @param string table
	* @param array fields to set to null
	* @param string conditions
	*/
	function updateNull($table, $nulls, $conditions = ''){
		if($this->dbaseType == 'mysql')
			$table = "`" . $table . "`";
		$sql = "UPDATE $table SET ";
		foreach($nulls as $null)
			$sqlArray[] = $this->formatField($null) . "=NULL";
		$this->error = $this->errors->text("dbError", "write");
		$this->query($sql . implode(", ", $sqlArray) . " $conditions");	
	}

	/*
	* @author Mark Grimshaw
	* @modified Daniel Pozzi
	* @version 1
	* @see SQL::listFields()
	* @param string table
	*/
	function listFields($table){
		if($this->dbaseType == 'mysql')
			$table = "`" . $table . "`";
		return $this->query("SHOW COLUMNS FROM $table");
	}
	
	/*
	* show all tables in db
	*
	* @author Daniel Pozzi
	* @version 2
	* @see SQL::listTables()
	* @param none
	* @return int
	*/
	function listTables(){
		return $this->db->MetaTables('TABLES', false, "WKX%");
	}
	
	/*
	* @author Mark Grimshaw
	* @modified Daniel Pozzi
	* @version 1
	* @see SQL::listFields()
	* @param string tables
	* @param conditions
	*/
	function delete($table, $conditions = ''){
		if($this->dbaseType == 'mysql')
			$table = "`" . $table . "`";
		$this->error = $this->errors->text("dbError", "write");
		$this->query("DELETE FROM $table $conditions");
	}
	
	/*
	* return last auto_increment ID
	*
	* @author Daniel Pozzi
	* @version 1
	* @param none
	* @return int/false
	*/
	function lastAutoID($table, $column = 'id'){
		return $this->db->insert_ID($table, $column);
	}
	
	/*
	* @author Mark Grimshaw
	* @modified Daniel Pozzi
	* @version 1
	* @param field string
	* @return string
	*/
	function formatField($string)
	{
		if($this->dbaseType == 'mysql')
			return "`" . str_replace(".", "`.`", $string) . "`";
// postgreSQL - WKX_config."contactEmail"
		$split = explode('.', $string);
		if(sizeof($split) == 2)
			return $split[0] . "." . "\"" . $split[1] . "\"";
		return "\"" . $string . "\"";
	}
	
	/*
	* @author Mark Grimshaw
	* @modified Daniel Pozzi
	* @version 1
	* @param array fields
	* @return string
	*/
	function formatFields($fields, $withExceptions = FALSE){

		foreach($fields as $field)
		{
			if(is_array($field))
			{
				if($withExceptions)
					$array[] = $this->formatAliasWithExceptions($field);
				else
					$array[] = $this->formatAlias($field);
			}
			else
			{
				if($this->dbaseType == 'mysql')
					$array[] = "`" . str_replace(".", "`.`", trim($field)) . "`";
				else
				{
// postgreSQL - WKX_config."contactEmail"
					$split = explode('.', trim($field));
					if(sizeof($split) == 2)
						$array[] = $split[0] . "." . "\"" . $split[1] . "\"";
					else
						$array[] = "\"" . trim($field) . "\"";
				}
			}
		}
		return implode(", ", $array);
	}
	
	/*
	*
	*/
	function formatTables($tables){

		foreach($tables as $table)
		{
			if($this->dbaseType == 'mysql')
				$array[] = "`" . trim($table) . "`";
			else
				$array[] = trim($table);
		}
		return implode(", ", $array);
	}
	
	/*
	*
	*/
	function formatTable($table){

		if($this->dbaseType == 'mysql')
			return "`" . trim($table) . "`";
		return trim($table);
	}
	
	/*
	*
	*/
	function formatValues($values){

		foreach($values as $value)
		{
			if(is_array($value))
				$array[] = $this->formatAlias($value);
			else
				$array[] = $this->tidyInput($value);
		}
		return implode(", ", $array);
	}
	
	/*
	* @author Mark Grimshaw
	* @modified Daniel Pozzi
	* @version 1
	* @see ADOSQL::update
	* @param array fields
	* @return string
	*/
	function formatUpdate($array){
		foreach($array as $field => $value)
		{
			if($this->dbaseType == 'mysql')
				$fieldArray[] = "`" . $field . "`" . "=" . $this->tidyInput($value);
			else
				$fieldArray[] = "\"" . $field . "\"" . "=" . $this->tidyInput($value);
		}
		return "SET " . implode(", ", $fieldArray);
	}
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param array fields
	* @return string
	*/
	function formatAlias($array)
	{
		list($key, $value) = each($array);
		if($this->dbaseType == 'mysql')
			return "`" . str_replace(".", "`.`", $key) . "` AS `$value`";
// postgreSQL - WKX_config."contactEmail"
		$split = explode('.', $key);
		if(sizeof($split) == 2)
			return $split[0] . "." . "\"" . $split[1] . "\"" . " AS " . "\"" . $value . "\"";
		return "\"" . $key . "\"" . " AS " . "\"" . $value . "\"";
	}
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param array fields
	* @return string
	*/
	function formatAliasWithExceptions($array)
	{
		list($key, $value) = each($array);
/**
* For something like DATE_FORMAT(timestamp,'%d/%b/%Y'), we don't want backticks.
* Add other exceptions here...
*/
		if(preg_match("/^DATE_FORMAT/", $key))
			return $key . " AS $value";
		return $key . " AS $value";
	}
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param string $UNIX epoch time
	*/
	function formatTimestamp($time = FALSE)
	{
		if(!$time)
			$time = time();
		return stripslashes(str_replace("'", "", $this->db->DBTimeStamp($time)));
	}
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param string $table
	*/
	function optimize($table)
	{
		if($this->dbaseType == 'mysql')
			$this->query("OPTIMIZE TABLE $table");
	}
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param string $string
	*/
	function tidyInput($string)
	{
		if($this->dbaseType == 'mysql')
			return "'" . mysql_real_escape_string(trim($string)) . "'";
		return "'" . pg_escape_string(trim($string)) . "'";
//		return $this->db->qstr(trim($string));
//		return $this->db->qstr(trim($string), get_magic_quotes_gpc());
	}
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param string $string
	*/
	function tidyInputNoTrim($string)
	{
		if($this->dbaseType == 'mysql')
			return "'" . mysql_real_escape_string($string) . "'";
		return "'" . pg_escape_string($string) . "'";
//		return $this->db->qstr(trim($string));
//		return $this->db->qstr(trim($string), get_magic_quotes_gpc());
	}
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param string $string
	*
	* For use with ORDER or GROUP by clauses
	*/
	function tidyInputClause($string)
	{
		if($this->dbaseType == 'mysql')
			return "`" . str_replace(".", "`.`", mysql_real_escape_string(trim($string))) . "`";
// postgreSQL - WKX_config."contactEmail"
		$split = explode('.', trim($string));
		if(sizeof($split) == 2)
			return $split[0] . "." . "\"" . pg_escape_string($split[1]) . "\"";
		return "\"" . pg_escape_string($string) . "\"";
//		return $this->db->qstr(trim($string));
//		return $this->db->qstr(trim($string), get_magic_quotes_gpc());
	}
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param string $string
	*/
	function concat($array)
	{
		if($this->dbaseType == 'mysql')
			return $this->db->Concat(implode(", ", $array));
		return join("||", $array);
	}
}

?>
